﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAL
{
    public class TheLoaiDAL
    {
        //lay ten the loai dua vao ma
        //return String
        //para: String ma
        //strTenTheLoai = "SELECT TenTheLoai FROM DBTheLoai WHERE MaTheLoai='" + strMaTheLoai + "'";
        public List <TheLoaiDTO> LayDanhSachDAL()
        {
            ///TẠO KẾT NỐI
            //string strcon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\SQL_QLSach.mdf;Integrated Security=True;User Instance=True";
            SqlConnection conn = new SqlConnection(DataProvidder.strConnectionfString);
            ///NO KÊT NỐI
            conn.Open();
            ///TRUY VẤN VÀ THỰC THI
            string strQuyer = "SELECT * FROM DBSach";
            SqlCommand cmd = new SqlCommand(strQuyer, conn);
            SqlDataReader dr = cmd.ExecuteReader();

            //dua ket qua vao danh sach thuc thi
            List<TheLoaiDTO> listtheloai = new List<TheLoaiDTO>();
            while (dr.Read())
            {
                TheLoaiDTO theloai = new TheLoaiDTO();
                theloai.MaTheLoai = dr.GetString(0);
                theloai.TenTheLoai = dr.GetString(1);
                listtheloai.Add(theloai);

            }
            return listtheloai;
        }
        public string LayTenTheLoaiDAL(string strMaTheLoai)
        {
            ///TẠO KẾT NỐI
            //string strcon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\SQL_QLSach.mdf;Integrated Security=True;User Instance=True";
            SqlConnection conn = new SqlConnection(DataProvidder.strConnectionfString);
            ///NO KÊT NỐI
            conn.Open();
            ///TRUY VẤN VÀ THỰC THI
            string strQuyer = "SELECT TenTheLoai FROM DBTheLoai WHERE MaTheLoai='" + strMaTheLoai + "'";
            SqlCommand cmd = new SqlCommand(strQuyer, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            string strTen = "";
            while (dr.Read())
            {
                TheLoaiDTO theloai = new TheLoaiDTO();
                theloai.TenTheLoai = dr.GetString(0);
                strTen = theloai.TenTheLoai;

            }
            return strTen;
        }
        public string LaymaTheLoaiDAL(string strTenTheLoai)
        {
            ///TẠO KẾT NỐI
            //string strcon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\SQL_QLSach.mdf;Integrated Security=True;User Instance=True";
            SqlConnection conn = new SqlConnection(DataProvidder.strConnectionfString);
            ///NO KÊT NỐI
            conn.Open();
            ///TRUY VẤN VÀ THỰC THI
            string strQuyer = "SELECT MaTheLoai FROM DBTheLoai WHERE TenTheLoai='" + strTenTheLoai + "'";
            SqlCommand cmd = new SqlCommand(strQuyer, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            string strTen = "";
            while (dr.Read())
            {
                TheLoaiDTO theloai = new TheLoaiDTO();
                theloai.MaTheLoai = dr.GetString(0);
                strTen = theloai.TenTheLoai;

            }
            return strTen;
        }
        //conbobox
        //note
        /* public class classes
        {
            private int id;
            private string name;

            public string Name
            {
                get { return name; }
                set { name = value; }
            }
    

            public int ID
            {
                get { return id; }
                set { id = value; }
            }

            public override string ToString()
            {
                return id.ToString();
            }
    
        
        //note
        private void Form1_Load(object sender, EventArgs e)
        {

            comboBox1.Items.Add("hello");//dòng muốn hiển thị
            for (int i = 0; i < LoadData().Count; i++)
            {
                comboBox1.Items.Add(LoadData()[i]);
                        }
            comboBox1.DisplayMember = "name";
                        

        }

        private List<classes> LoadData()
        {
            SqlConnection conn = new SqlConnection(@"server=.\sqlexpress;database=schools;integrated security=true");
            conn.Open();
            SqlDataReader reader = new SqlCommand("select * from class", conn).ExecuteReader();
            List<classes> list = new List<classes>();
            classes cl;
            while (reader.Read())
            {
                cl = new classes();
                cl.ID = reader.GetInt32(0);
                cl.Name = reader.GetString(1);
                list.Add(cl);

            }
            return list;
        }*/

        /*Bước 1: Trước tiên tạo đối tượng Sinh Viên.
class SinhVien
{
private int id;

public int Id
{
get{ return id; }
set { id = value; }
}
private string name;

public string Name
{
get{ return name; }
set { name = value; }
}
private string lop;

public string Lop
{
get { return lop; }
set { lop = value; }
}
private string khoa;

public string Khoa
{
get { return khoa; }
set { khoa = value; }
}
}

Bươc 2: Đổ dữ liệu vào Combobox.

string conn = "Data Source=.\\SqlExpress;Initial Catalog=Restaurant;Integrated Security=True";

SqlConnection connect = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select * from SinhVien";
cmd.CommandType = CommandType.Text;
cmd.Connection = connect;

connect.Open();
DataTable result = new DataTable();
SqlDataAdapter dapter = new SqlDataAdapter(cmd); 
dapter.Fill(result);
connect.Close();

List<SinhVien> sinhviens = new List<SinhVien>();
foreach (DataRow item in result.Rows)
{
SinhVien sv = new SinhVien();
sv.Id = (int)item["Id"];
sv.Name = item["Name"].ToString();
sv.Lop = item["Lop"].ToString();
sv.Khoa = item["Khoa"].ToString();

sinhviens.Add(sv);
}
cboSinhVien.DataSource = sinhviens;
cboSinhVien.DisplayMember = "Name";

Bước 3: Sau khi hiển thị dữ liêu vào combobox. Ta viết code xử lý cho sự kiên SelectedIndexChanged của Combobox

SinhVien sinhvien = ((SinhVien)cboSinhVien.SelectedItem);

txtSinhVienID.Text = sinhvien.ID.ToString();
txtTenSinhVien.Text = sinhvien.Name.ToString();
txtLop.Text = sinhvien.Lop.ToString();
txtKhoa.Text = sinhvien.Khoa.ToString();*/
        
        /*lam vay lau wa'. 
SqlConnection sqlcon = new SqlConnection("Server=CHANH_PC;Database=win2OrderN ote;Uid=sa;Pwd=123");

SqlCommand sqlcom = new SqlCommand("select * from Product", sqlcon);
sqlcon.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlcom);
DataSet ds = new DataSet();
da.Fill(ds, "Product");
//laod into comobox
DataTable dt = ds.Tables["Product"];
//
this.comboBox1.DataSource = ds.Tables[0];
comboBox1.DisplayMember = "ProductName";
comboBox1.ValueMember = "ProductID";
//
textBox1.DataBindings.Add("Text", ds.Tables[0], "UnitPrice");// muon bao nhieu cot thj them vao thoi!*/
    }
}
